﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using BOHVote.Core;

namespace BOHVote.DAL
{
    public class SubjectProvide
    {
        Database db;
        public SubjectProvide()
        {
            db = DatabaseFactory.CreateDatabase();
        }

        #region <<  Subject  >>
        public DataSet getAllSubject(string ID,bool IsALL)
        {
            string strSQL = "SELECT * FROM Subject WHERE 1=1";
            if (ID.Trim().Length > 0)
            {
                strSQL += " AND ID=" + ID; 
            }
            if (!IsALL)
            {
                strSQL += " AND status = 'A' ";
            }

            strSQL += "  ORDER BY SubjectID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public string getSubjectNameBySubjectID(string subjectID)
        {
            string strSQL = "SELECT SubjectName FROM Subject WHERE 1=1 AND SubjectID = '" + subjectID + "'";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }

        public DataSet getAllSubject(string SubjectName, string status, string AccreditationLevel)
        {
            string strSQL = "SELECT * FROM Subject WHERE 1=1";
            if (SubjectName.Trim().Length > 0)
            {
                strSQL += " AND SubjectName like '%" + SubjectName+"%' " ;
            }
            if (status.Trim().Length>0)
            {
                strSQL += " AND status = '" + status + "' ";
            }

            if (!string.IsNullOrEmpty(AccreditationLevel))
            {
                strSQL += " AND AccreditationLevel = '" + AccreditationLevel + "' ";
            }

            strSQL += "  ORDER BY OrderID ASC,SubjectID";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int DeleteSubject(string ID)
        {
            string strSQL = " DELETE FROM Subject WHERE ID = "+ID ;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int InsertSubject(string SubjectID, string SubjectName, string Status, string Level,int orderID)
        {
            string strSQL = "INSERT INTO Subject(SubjectID, SubjectName,  Status, AccreditationLevel,OrderID)VALUES('" + SubjectID + "', '" + SubjectName + "',  '" + Status + "', '" + Level + "','" + orderID + "') ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateSubject(string ID, string SubjectID, string SubjectName, string Status, string Level,int orderID)
        {
            string strSQL = "UPDATE Subject SET SubjectID='" + SubjectID + "', SubjectName='" + SubjectName + "',orderID='" + orderID + "', Status = '" + Status + "', AccreditationLevel='" + Level + "' WHERE ID=" + ID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);

        }

        #endregion

        #region <<  Speciality  >>
        public DataSet getSpeciality(string ID, string status)
        {
            string strSQL = "SELECT * FROM Speciality WHERE 1=1";
            if (ID.Trim().Length > 0)
            {
                strSQL += " AND ID=" + ID;
            }
            if (status.Trim().Length>0)
            {
                strSQL += " AND status = '" + status + "' ";
            }

            strSQL += "  ORDER BY SpecialityID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getASpecialityBySubject(string SubjectID)
        {
            return getSpecialityBySubject(SubjectID, "A");
        }

        public DataSet getDSpecialityBySubject(string SubjectID)
        {
            return getSpecialityBySubject(SubjectID, "D");
        }

        public DataSet getAllSpecialityBySubject(string SubjectID)
        {
            return getSpecialityBySubject(SubjectID, "");
        }

        public DataSet getSpecialityBySubject(string SubjectID,string status)
        {
            string strSQL = "SELECT * FROM Speciality WHERE 1=1";
            if (SubjectID.Trim().Length > 0)
            {
                strSQL += " AND SubjectID='" + SubjectID+"'";
            }
            if (status.Trim().Length > 0)
            {
                strSQL += " AND status = '" + status + "' ";
            }

            strSQL += "  ORDER BY SpecialityID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getAllSpeciality(string SpecialityName, string SubjectID, string status)
        {
            string strSQL = "SELECT * FROM Speciality WHERE 1=1";
            if (SpecialityName.Trim().Length > 0)
            {
                strSQL += " AND SpecialityName like '%" + SpecialityName + "%' ";
            }
            if (SubjectID.Trim().Length > 0)
            {
                strSQL += " AND SubjectID = '" + SubjectID + "' ";
            }
            if (status.Trim().Length > 0)
            {
                strSQL += " AND status = '" + status + "' ";
            }

            strSQL += "  ORDER BY SpecialityID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public string getSpecialityName(string SpecialityID)
        {
            string strSQL = "SELECT SpecialityName FROM Speciality WHERE SpecialityID = '" + SpecialityID + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }

        public int InsertSpeciality(string SpecialityID, string SpecialityName, string SubjectID, string status)
        {
            string strSQL = "INSERT INTO Speciality (SpecialityID,SpecialityName,SubjectID,status)VALUES('"+SpecialityID+"','"+SpecialityName+"','"+SubjectID+"','"+status+"') ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateSpeciality(string ID,string SpecialityID, string SpecialityName, string SubjectID, string status)
        {
            string strSQL = "UPDATE Speciality SET SpecialityID = '" + SpecialityID + "',SpecialityName= '" + SpecialityName + "',SubjectID= '" + SubjectID + "',status= '" + status + "' WHERE ID = " + ID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int DeleteSpeciality(string ID)
        {
            string strSQL = "DELETE FROM Speciality WHERE ID=" + ID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }
        #endregion

        #region <<  Test Speciality  >>
        public DataSet getTestSpeciality(string ID, string status)
        {
            string strSQL = "SELECT * FROM TestSpeciality WHERE 1=1";
            if (ID.Trim().Length > 0)
            {
                strSQL += " AND ID=" + ID;
            }
            if (status.Trim().Length > 0)
            {
                strSQL += " AND status = '" + status + "' ";
            }

            strSQL += "  ORDER BY TestSpecialityID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getATestSpecialityBySubject(string SubjectID)
        {
            return getTestSpecialityBySubject(SubjectID, "A");
        }

        public DataSet getDTestSpecialityBySubject(string SubjectID)
        {
            return getTestSpecialityBySubject(SubjectID, "D");
        }

        public DataSet getAllTestSpecialityBySubject(string SubjectID)
        {
            return getTestSpecialityBySubject(SubjectID, "");
        }

        public DataSet getTestSpecialityBySubject(string SubjectID, string status)
        {
            string strSQL = "SELECT * FROM TestSpeciality WHERE 1=1";
            if (SubjectID.Trim().Length > 0)
            {
                strSQL += " AND SubjectID='" + SubjectID + "'";
            }
            if (status.Trim().Length > 0)
            {
                strSQL += " AND status = '" + status + "' ";
            }

            strSQL += "  ORDER BY TestSpecialityID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getAllTestSpeciality(string SpecialityName, string SubjectID, string status)
        {
            string strSQL = "SELECT * FROM TestSpeciality WHERE 1=1";
            if (SpecialityName.Trim().Length > 0)
            {
                strSQL += " AND TestSpecialityName like '%" + SpecialityName + "%' ";
            }
            if (SubjectID.Trim().Length > 0)
            {
                strSQL += " AND SubjectID = '" + SubjectID + "' ";
            }
            if (status.Trim().Length > 0)
            {
                strSQL += " AND status = '" + status + "' ";
            }

            strSQL += "  ORDER BY TestSpecialityID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public string getTestSpecialityName(string TestSpecialityID)
        {
            string strSQL = "SELECT TestSpecialityName FROM TestSpeciality WHERE TestSpecialityID = '" + TestSpecialityID + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
        }

        public int InsertTestSpeciality(string TestSpecialityID, string TestSpecialityName, string SubjectID, string status)
        {
            string strSQL = "INSERT INTO TestSpeciality (TestSpecialityID,TestSpecialityName,SubjectID,status)VALUES('" + TestSpecialityID + "','" + TestSpecialityName + "','" + SubjectID + "','" + status + "') ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateTestSpeciality(string ID, string TestSpecialityID, string TestSpecialityName, string SubjectID, string status)
        {
            string strSQL = "UPDATE TestSpeciality SET TestSpecialityID = '" + TestSpecialityID + "',TestSpecialityName= '" + TestSpecialityName + "',SubjectID= '" + SubjectID + "',status= '" + status + "' WHERE ID = " + ID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int DeleteTestSpeciality(string ID)
        {
            string strSQL = "DELETE FROM TestSpeciality WHERE ID=" + ID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            return Utility.ExecuteNonQuery(db, dbcommand);
        }
        #endregion
    }
}
